ALTER TABLE FileGroup
ADD FileHash INT
GO

CREATE NONCLUSTERED INDEX IX_FileGroup_FileHash
ON FileGroup(FileHash ASC)
GO

ALTER TABLE EmailGroupUserAccount
ADD CONSTRAINT CU_EmailGroupUserAccount_EmailGroupID_UserAccountID UNIQUE (EmailGroupID,UserAccountID)
GO

ALTER TABLE EmailGroupSecurityGroup
ADD CONSTRAINT CU_EmailGroupSecurityGroup_EmailGroupID_SecurityGroupID UNIQUE (EmailGroupID,SecurityGroupID)
GO

ALTER TABLE EmailGroupMeterGroup
ADD CONSTRAINT CU_EmailGroupMeterGroup_EmailGroupID_MeterGroupID UNIQUE (EmailGroupID,MeterGroupID)
GO

ALTER TABLE EmailGroupType
ADD CONSTRAINT CU_EmailGroupType_EmailGroupID_EmailTypeID UNIQUE (EmailGroupID,EmailTypeID)
GO

ALTER TABLE FaultEmailCriterion
ADD EmailOnReclose INT NOT NULL DEFAULT 0
GO

ALTER TABLE EventData
DROP COLUMN FrequencyDomainData
GO

ALTER TABLE EventType
ADD CONSTRAINT UQ_EventType_Name UNIQUE(Name)
GO

ALTER TABLE Event
ALTER COLUMN EventDataID INT NULL
GO

ALTER TABLE Event
ADD UpdatedBy VARCHAR(200) NULL
GO

ALTER TABLE Disturbance
ADD UpdatedBy VARCHAR(200) NULL
GO

ALTER TABLE WorkbenchFilter
ADD Lines VARCHAR(MAX) NULL
GO

ALTER TABLE BreakerOperationType
ADD CONSTRAINT UQ_BreakerOperationType_Name UNIQUE(Name)
GO

ALTER TABLE BreakerOperation
ADD StatusBitChatter INT NOT NULL
GO

ALTER TABLE BreakerOperation
ADD StatusTiming FLOAT NOT NULL
GO

ALTER TABLE BreakerOperation
ADD DcOffsetDetected INT NOT NULL
GO

ALTER TABLE BreakerOperation
ADD UpdatedBy VARCHAR(50) NULL
GO

ALTER TABLE LineImpedance
ADD CONSTRAINT UQ_LineImpedance_LineID UNIQUE(LineID)
GO

ALTER TABLE SegmentType
ADD CONSTRAINT UQ_SegmentType_Name UNIQUE(Name)
GO

ALTER TABLE FaultSummary
ADD ReactanceRatio FLOAT NOT NULL
GO

ALTER TABLE DoubleEndedFaultDistance
ADD CONSTRAINT UQ_DoubleEndedFaultDistance_LocalFaultSummaryID UNIQUE(LocalFaultSummaryID)
GO

ALTER TABLE DoubleEndedFaultDistance
ADD CONSTRAINT UQ_DoubleEndedFaultDistance_RemoteFaultSummaryID UNIQUE(RemoteFaultSummaryID)
GO

ALTER TABLE EASExtension
ADD WebPage VARCHAR(MAX) NOT NULL
GO

ALTER FUNCTION GetSystemEventIDs
(
    @startTime DATETIME2,
    @endTime DATETIME2,
    @timeTolerance FLOAT
)
RETURNS @systemEvent TABLE
(
    EventID INT
)
AS BEGIN
    DECLARE @adjustedStartTime DATETIME2 = dbo.AdjustDateTime2(@startTime, -@timeTolerance)
    DECLARE @adjustedEndTime DATETIME2 = dbo.AdjustDateTime2(@endTime, @timeTolerance)
    DECLARE @minStartTime DATETIME2
    DECLARE @maxEndTime DATETIME2

    SELECT
        @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)),
        @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance))
    FROM
        Event JOIN
        FileGroup ON Event.FileGroupID = FileGroup.ID
    WHERE
        StartTime <= @adjustedEndTime AND
        @adjustedStartTime <= EndTime AND
        ProcessingEndTime > '0001-01-01'

    WHILE @startTime != @minStartTime OR @endTime != @maxEndTime
    BEGIN
        SET @startTime = @minStartTime
        SET @endTime = @maxEndTime
        SET @adjustedStartTime = dbo.AdjustDateTime2(@startTime, -@timeTolerance)
        SET @adjustedEndTime = dbo.AdjustDateTime2(@endTime, @timeTolerance)

        SELECT
            @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)),
            @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance))
        FROM
            Event JOIN
            FileGroup ON Event.FileGroupID = FileGroup.ID
        WHERE
            StartTime <= @adjustedEndTime AND
            @adjustedStartTime <= EndTime AND
            ProcessingEndTime > '0001-01-01'
    END

    INSERT INTO @systemEvent
    SELECT ID
    FROM Event
    WHERE @adjustedStartTime <= StartTime AND EndTime <= @adjustedEndTime

    RETURN
END
GO

ALTER VIEW MeterDetail
AS
SELECT	Meter.ID, 
		Meter.AssetKey, 
		Meter.MeterLocationID, 
		MeterLocation.AssetKey AS LocationKey, 
		MeterLocation.Name AS Location, 
		MeterLocation.Latitude, 
		MeterLocation.Longitude, 
        Meter.Name, 
		Meter.Alias, 
		Meter.ShortName, 
		Meter.Make, 
		Meter.Model, 
		CASE COALESCE (Meter.TimeZone, '') 
			WHEN '' THEN COALESCE (Setting.Value, 'UTC') 
            ELSE Meter.TimeZone END AS TimeZone, 
		Meter.Description
FROM    Meter INNER JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
        Setting ON Setting.Name = 'DefaultMeterTimeZone'

GO

ALTER VIEW ChannelDetail
AS
SELECT
    Channel.ID,
    Channel.MeterID,
    Meter.AssetKey AS MeterKey,
    Meter.Name AS MeterName,
    Channel.LineID,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    Channel.MeasurementTypeID,
    MeasurementType.Name AS MeasurementType,
    Channel.MeasurementCharacteristicID,
    MeasurementCharacteristic.Name AS MeasurementCharacteristic,
    Channel.PhaseID,
    Phase.Name AS Phase,
    Channel.Name,
    Channel.SamplesPerHour,
    Channel.PerUnitValue,
    Channel.HarmonicGroup,
    Series.SourceIndexes AS Mapping,
    Channel.Description,
    Channel.Enabled,
    Series.SeriesTypeID,
    SeriesType.Name AS SeriesType
FROM
    Channel JOIN
    Meter ON Channel.MeterID = Meter.ID JOIN
    Line ON Channel.LineID = Line.ID JOIN
    MeterLine ON
        MeterLine.MeterID = Meter.ID AND
        MeterLine.LineID = Line.ID JOIN
    MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
    MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
    Phase ON Channel.PhaseID = Phase.ID LEFT OUTER JOIN
    Series ON
        Series.ChannelID = Channel.ID AND
        Series.SourceIndexes <> '' LEFT OUTER JOIN
    SeriesType ON dbo.Series.SeriesTypeID = dbo.SeriesType.ID
GO

ALTER VIEW DefaultAlarmRangeLimitView
AS
SELECT
    DefaultAlarmRangeLimit.ID,
    DefaultAlarmRangeLimit.MeasurementTypeID,
    DefaultAlarmRangeLimit.AlarmTypeID,
    DefaultAlarmRangeLimit.MeasurementCharacteristicID,
    DefaultAlarmRangeLimit.Severity,
    DefaultAlarmRangeLimit.High,
    DefaultAlarmRangeLimit.Low,
    DefaultAlarmRangeLimit.PerUnit,
    DefaultAlarmRangeLimit.RangeInclusive,
    AlarmType.Name AS AlarmType,
    MeasurementCharacteristic.Name AS MeasurementCharacteristic,
    MeasurementType.Name AS MeasurementType
FROM
    DefaultAlarmRangeLimit JOIN
    AlarmType ON DefaultAlarmRangeLimit.AlarmTypeID = AlarmType.ID JOIN
    MeasurementCharacteristic ON DefaultAlarmRangeLimit.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
    MeasurementType ON DefaultAlarmRangeLimit.MeasurementTypeID = MeasurementType.ID
GO

ALTER VIEW AlarmRangeLimitView
AS
SELECT
    AlarmRangeLimit.ID,
    AlarmRangeLimit.ChannelID,
    Channel.MeterID,
    Channel.LineID,
    Channel.Name,
    AlarmRangeLimit.AlarmTypeID,
    AlarmRangeLimit.Severity,
    AlarmRangeLimit.High,
    AlarmRangeLimit.Low,
    AlarmRangeLimit.RangeInclusive,
    AlarmRangeLimit.PerUnit,
    AlarmRangeLimit.Enabled,
    MeasurementType.Name AS MeasurementType,
    MeasurementCharacteristic.Name AS MeasurementCharacteristic,
    Phase.Name AS Phase,
    Channel.HarmonicGroup,
    Channel.MeasurementTypeID,
    Channel.MeasurementCharacteristicID,
    Channel.PhaseID,
    AlarmRangeLimit.IsDefault,
    Meter.Name AS MeterName
FROM
    AlarmRangeLimit JOIN
    Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN
    MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
    MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
    Phase ON Channel.PhaseID = Phase.ID JOIN
    Meter ON Channel.MeterID = Meter.ID
GO

ALTER VIEW UserMeter
AS
SELECT DISTINCT
    UserAccount.Name AS UserName,
    Meter.ID AS MeterID
FROM
    Meter JOIN
    MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID JOIN
    UserAccountMeterGroup ON MeterMeterGroup.MeterGroupID = UserAccountMeterGroup.MeterGroupID JOIN
    UserAccount ON UserAccountMeterGroup.UserAccountID = UserAccount.ID
GO

ALTER VIEW EventDetail AS
WITH TimeTolerance AS
(
    SELECT
        COALESCE(CAST(Value AS FLOAT), 0.5) AS Tolerance
    FROM
        (SELECT 'TimeTolerance' AS Name) AS SettingName LEFT OUTER JOIN
        Setting ON SettingName.Name = Setting.Name
),
SelectedDisturbance AS
(
    SELECT Disturbance.*
    FROM
        Disturbance JOIN
        Phase ON Disturbance.PhaseID = Phase.ID JOIN
        Disturbance WorstDisturbance ON
            Disturbance.EventID = WorstDisturbance.EventID AND
            Disturbance.Magnitude = WorstDisturbance.Magnitude AND
            Disturbance.DurationCycles = WorstDisturbance.DurationCycles JOIN
        Phase WorstPhase ON WorstDisturbance.PhaseID = WorstPhase.ID
    WHERE
        Phase.Name <> 'Worst' AND
        WorstPhase.Name = 'Worst'
),
DisturbanceData AS
(
    SELECT
        SelectedDisturbance.ID AS DisturbanceID,
        Meter.AssetKey AS MeterKey,
        MeterLocation.Name AS StationName,
        MeterLine.LineName,
        EventType.Name AS DisturbanceType,
        Phase.Name AS Phase,
        SelectedDisturbance.StartTime,
        SelectedDisturbance.EndTime,
        SelectedDisturbance.DurationCycles,
        SelectedDisturbance.DurationSeconds * 1000.0 AS DurationMilliseconds,
        SelectedDisturbance.Magnitude,
        SelectedDisturbance.PerUnitMagnitude,
        RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName,
        SelectedDisturbance.EventID,
        Event.StartTime AS EventStartTime,
        Event.EndTime AS EventEndTime
    FROM
        SelectedDisturbance JOIN
        Event ON SelectedDisturbance.EventID = Event.ID JOIN
        EventType ON SelectedDisturbance.EventTypeID = EventType.ID JOIN
        DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
        Meter ON Event.MeterID = Meter.ID JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
        MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN
        Phase ON SelectedDisturbance.PhaseID = Phase.ID
    WHERE
        DataFile.FilePath LIKE '%.DAT' OR
        DataFile.FilePath LIKE '%.D00' OR
        DataFile.FilePath LIKE '%.PQD' OR
        DataFile.FilePath LIKE '%.RCD' OR
        DataFile.FilePath LIKE '%.RCL' OR
        DataFile.FilePath LIKE '%.SEL' OR
        DataFile.FilePath LIKE '%.EVE' OR
        DataFile.FilePath LIKE '%.CEV'
),
SelectedSummary AS
(
    SELECT *
    FROM FaultSummary
    WHERE IsSelectedAlgorithm <> 0 AND IsSuppressed = 0
),
SummaryData AS
(
    SELECT
        SelectedSummary.ID AS FaultSummaryID,
        Meter.AssetKey AS MeterKey,
        Meter.Name AS MeterName,
        MeterLocation.AssetKey as StationKey,
        MeterLocation.Name AS StationName,
        Line.AssetKey AS LineKey,
        MeterLine.LineName,
        SelectedSummary.FaultType,
        SelectedSummary.Inception,
        SelectedSummary.DurationCycles,
        SelectedSummary.DurationSeconds * 1000.0 AS DurationMilliseconds,
        SelectedSummary.PrefaultCurrent,
        SelectedSummary.PostfaultCurrent,
        SelectedSummary.ReactanceRatio,
        SelectedSummary.CurrentMagnitude AS FaultCurrent,
        SelectedSummary.Algorithm,
        SelectedSummary.Distance AS SingleEndedDistance,
        DoubleEndedFaultSummary.Distance AS DoubleEndedDistance,
        DoubleEndedFaultSummary.Angle AS DoubleEndedAngle,
        RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName,
        SelectedSummary.EventID,
        Event.StartTime AS EventStartTime,
        Event.EndTime AS EventEndTime,
        (Select Distance FROM FaultSummary as fs WHERE fs.Algorithm = 'Simple' AND fs.EventID = SelectedSummary.EventID AND fs.Inception = SelectedSummary.Inception) as Simple,
        (Select Distance FROM FaultSummary as fs WHERE fs.Algorithm = 'Reactance' AND fs.EventID = SelectedSummary.EventID AND fs.Inception = SelectedSummary.Inception) as Reactance
    FROM
        SelectedSummary JOIN
        Event ON SelectedSummary.EventID = Event.ID JOIN
        DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
        Meter ON Event.MeterID = Meter.ID JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
        MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN
        Line ON Line.ID=MeterLine.LineID LEFT OUTER JOIN
        DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = SelectedSummary.ID LEFT OUTER JOIN
        DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID
    WHERE
        DataFile.FilePath LIKE '%.DAT' OR
        DataFile.FilePath LIKE '%.D00' OR
        DataFile.FilePath LIKE '%.PQD' OR
        DataFile.FilePath LIKE '%.RCD' OR
        DataFile.FilePath LIKE '%.RCL' OR
        DataFile.FilePath LIKE '%.SEL' OR
        DataFile.FilePath LIKE '%.EVE' OR
        DataFile.FilePath LIKE '%.CEV'
),
SummaryIDs AS
(
    SELECT
        SelectedSummary.ID AS FaultSummaryID,
        EventID,
        LineID,
        MeterID AS PartitionID,
        Inception AS OrderID
    FROM
        SelectedSummary JOIN
        Event ON SelectedSummary.EventID = Event.ID
)
SELECT
    Event.ID AS EventID,
    (
        SELECT
            Event.ID AS [Event/ID],
            Event.StartTime AS [Event/StartTime],
            Event.EndTime AS [Event/EndTime],
            EventType.Name AS [Event/Type],
            (
                SELECT
                    SequenceNumber AS [@num],
                    (
                        SELECT
                            MeterKey,
                            StationName,
                            LineName,
                            DisturbanceType,
                            Phase,
                            StartTime,
                            EndTime,
                            DurationCycles,
                            DurationMilliseconds,
                            Magnitude,
                            PerUnitMagnitude,
                            EventStartTime,
                            EventEndTime,
                            FileName,
                            EventID,
                            DisturbanceID
                        FROM DisturbanceData
                        WHERE DisturbanceID IN
                        (
                            SELECT PhaseDisturbance.ID
                            FROM
                                dbo.GetDisturbancesInSystemEvent(Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance)) InnerSequenceNumber JOIN
                                Disturbance ON Disturbance.ID = InnerSequenceNumber.ID JOIN
                                Disturbance PhaseDisturbance ON
                                    Disturbance.EventID = PhaseDisturbance.EventID AND
                                    Disturbance.StartTime = PhaseDisturbance.StartTime AND
                                    Disturbance.PerUnitMagnitude = PhaseDisturbance.PerUnitMagnitude
                            WHERE InnerSequenceNumber.SequenceNumber = OuterSequenceNumber.SequenceNumber
                        )
                        ORDER BY DisturbanceData.StartTime
                        FOR XML PATH('Disturbance'), TYPE
                    )
                FROM
                (
                    SELECT DISTINCT SequenceNumber
                    FROM dbo.GetDisturbancesInSystemEvent(Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance))
                ) OuterSequenceNumber
                FOR XML PATH('DisturbanceGroup'), TYPE
            ) AS [DisturbanceGroups],
            (
                SELECT
                    FaultNumber AS [@num],
                    (
                        SELECT
                            MeterKey,
                            MeterName,
                            StationKey,
                            StationName,
                            LineKey,
                            LineName,
                            FaultType,
                            Inception,
                            DurationCycles,
                            DurationMilliseconds,
                            PrefaultCurrent,
                            PostfaultCurrent,
                            ReactanceRatio,
                            FaultCurrent,
                            Algorithm,
                            SingleEndedDistance,
                            DoubleEndedDistance,
                            DoubleEndedAngle,
                            EventStartTime,
                            EventEndTime,
                            FileName,
                            EventID,
                            FaultSummaryID AS FaultID,
                            CASE WHEN ABS(Reactance/COALESCE(Simple,1)) > 0.6 THEN 'LOW'
                                 WHEN ABS(Reactance/COALESCE(Simple,1)) < 0.4 THEN 'HIGH'
                                 ELSE 'MEDIUM'
                            END AS Ratio
                        FROM SummaryData
                        WHERE FaultSummaryID IN
                        (
                            SELECT FaultSummaryID
                            FROM
                            (
                                SELECT FaultSummaryID, ROW_NUMBER() OVER(PARTITION BY PartitionID ORDER BY OrderID) AS FaultNumber
                                FROM SummaryIDs
                                WHERE SummaryIDs.LineID = Event.LineID AND SummaryIDs.EventID IN (SELECT * FROM dbo.GetLineEventIDs(Event.LineID, Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance)))
                            ) InnerFaultNumber
                            WHERE InnerFaultNumber.FaultNumber = OuterFaultNumber.FaultNumber
                        )
                        FOR XML PATH('SummaryData'), TYPE
                    )
                FROM
                (
                    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY PartitionID ORDER BY OrderID) AS FaultNumber
                    FROM SummaryIDs
                    WHERE SummaryIDs.LineID = Event.LineID AND SummaryIDs.EventID IN (SELECT * FROM dbo.GetLineEventIDs(Event.LineID, Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance)))
                ) OuterFaultNumber
                FOR XML PATH('Fault'), TYPE
            ) AS [Faults],
            Meter.AssetKey AS [Meter/AssetKey],
            Meter.Name AS [Meter/Name],
            Meter.ShortName AS [Meter/ShortName],
            Meter.Alias AS [Meter/Alias],
            Meter.Make AS [Meter/Make],
            Meter.Model AS [Meter/Model],
            MeterLocation.AssetKey AS [MeterLocation/AssetKey],
            MeterLocation.Name AS [MeterLocation/Name],
            MeterLocation.ShortName AS [MeterLocation/ShortName],
            MeterLocation.Alias AS [MeterLocation/Alias],
            SourceImpedance.RSrc AS [MeterLocation/RSrc],
            SourceImpedance.XSrc AS [MeterLocation/XSrc],
            Line.AssetKey AS [Line/AssetKey],
            MeterLine.LineName AS [Line/Name],
			Line.Description AS [Line/Description],
            FORMAT(Line.Length, '0.##########') AS [Line/Length],
            FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), '0.##########') AS [Line/Z1],
            CASE LineImpedance.R1 WHEN 0 THEN '0' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), '0.##########') END AS [Line/A1],
            FORMAT(LineImpedance.R1, '0.##########') AS [Line/R1],
            FORMAT(LineImpedance.X1, '0.##########') AS [Line/X1],
            FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), '0.##########') AS [Line/Z0],
            CASE LineImpedance.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), '0.##########') END AS [Line/A0],
            FORMAT(LineImpedance.R0, '0.##########') AS [Line/R0],
            FORMAT(LineImpedance.X0, '0.##########') AS [Line/X0],
            FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), '0.##########') AS [Line/ZS],
            CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), '0.##########') END AS [Line/AS],
            FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, '0.##########') AS [Line/RS],
            FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, '0.##########') AS [Line/XS],
            (
                CAST((SELECT '<TimeTolerance>' + CAST((SELECT * FROM TimeTolerance) AS VARCHAR) + '</TimeTolerance>') AS XML)
            ) AS [Settings]
        FROM
            Meter CROSS JOIN
            Line LEFT OUTER JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID LEFT OUTER JOIN
            MeterLocationLine ON MeterLocationLine.MeterLocationID = MeterLocation.ID AND MeterLocationLine.LineID = Line.ID LEFT OUTER JOIN
            SourceImpedance ON SourceImpedance.MeterLocationLineID = MeterLocationLine.ID LEFT OUTER JOIN
            LineImpedance ON LineImpedance.LineID = Line.ID LEFT OUTER JOIN
            EventType ON Event.EventTypeID = EventType.ID
        WHERE
            Event.MeterID = Meter.ID AND
            Event.LineID = Line.ID
        FOR XML PATH('EventDetail'), TYPE
    ) AS EventDetail
FROM Event
GO

ALTER VIEW EventView
AS
SELECT
    Event.ID,
    Event.FileGroupID,
    Event.MeterID,
    Event.LineID,
    Event.EventTypeID,
    Event.EventDataID,
    Event.Name,
    Event.Alias,
    Event.ShortName,
    Event.StartTime,
    Event.EndTime,
    Event.Samples,
    Event.TimeZoneOffset,
    Event.SamplesPerSecond,
    Event.SamplesPerCycle,
    Event.Description,
    Event.UpdatedBy,
    MeterLine.LineName,
    Meter.Name AS MeterName,
    MeterLocation.Name AS StationName,
    Line.Length,
    EventType.Name AS EventTypeName
FROM
    Event JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID
GO

ALTER VIEW [dbo].[DisturbanceView]
AS
SELECT dbo.Disturbance.ID, dbo.Disturbance.EventID, dbo.Disturbance.EventTypeID, dbo.Disturbance.PhaseID, dbo.Disturbance.Magnitude, dbo.Disturbance.PerUnitMagnitude, dbo.Disturbance.StartTime,
       dbo.Disturbance.EndTime, dbo.Disturbance.DurationSeconds, dbo.Disturbance.DurationCycles, dbo.Disturbance.StartIndex, dbo.Disturbance.EndIndex, dbo.Event.MeterID,
           (SELECT        MAX(SeverityCode) AS Expr1
             FROM            dbo.DisturbanceSeverity
             WHERE        (DisturbanceID = dbo.Disturbance.ID)) AS SeverityCode, dbo.Meter.Name AS MeterName, dbo.Phase.Name AS PhaseName, dbo.Event.LineID
FROM   dbo.Disturbance INNER JOIN
       dbo.Event ON dbo.Disturbance.EventID = dbo.Event.ID INNER JOIN
       dbo.Meter ON dbo.Event.MeterID = dbo.Meter.ID INNER JOIN
       dbo.Phase ON dbo.Disturbance.PhaseID = dbo.Phase.ID

GO

ALTER VIEW [dbo].[BreakerView]
AS
SELECT dbo.BreakerOperation.ID, dbo.Meter.ID AS MeterID, dbo.Event.ID AS EventID, dbo.EventType.Name AS EventType, dbo.BreakerOperation.TripCoilEnergized AS Energized, dbo.BreakerOperation.BreakerNumber,
       dbo.MeterLine.LineName, dbo.Phase.Name AS PhaseName, CAST(dbo.BreakerOperation.BreakerTiming AS DECIMAL(16, 5)) AS Timing, dbo.BreakerOperation.BreakerSpeed AS Speed,
       dbo.BreakerOperationType.Name AS OperationType, BreakerOperation.UpdatedBy
FROM   dbo.BreakerOperation INNER JOIN
       dbo.Event ON dbo.BreakerOperation.EventID = dbo.Event.ID INNER JOIN
       dbo.EventType ON dbo.EventType.ID = dbo.Event.EventTypeID INNER JOIN
       dbo.Meter ON dbo.Meter.ID = dbo.Event.MeterID INNER JOIN
       dbo.Line ON dbo.Line.ID = dbo.Event.LineID INNER JOIN
       dbo.MeterLine ON dbo.MeterLine.LineID = dbo.Event.LineID AND dbo.MeterLine.MeterID = dbo.Meter.ID INNER JOIN
       dbo.BreakerOperationType ON dbo.BreakerOperation.BreakerOperationTypeID = dbo.BreakerOperationType.ID INNER JOIN
       dbo.Phase ON dbo.BreakerOperation.PhaseID = dbo.Phase.ID

GO

ALTER VIEW [dbo].[FaultView]
AS
SELECT
            FaultSummary.ID AS ID,
            FaultSummary.EventID,
            FaultSummary.Algorithm,
            FaultSummary.FaultNumber,
            FaultSummary.CalculationCycle,
            FaultSummary.Distance,
            FaultSummary.CurrentMagnitude,
            FaultSummary.CurrentLag,
            FaultSummary.PrefaultCurrent,
            FaultSummary.PostfaultCurrent,
            FaultSummary.Inception,
            FaultSummary.DurationSeconds,
            FaultSummary.DurationCycles,
            FaultSummary.FaultType,
            FaultSummary.IsSelectedAlgorithm,
            FaultSummary.IsValid,
            FaultSummary.IsSuppressed,
            Meter.Name AS MeterName,
            Meter.ShortName AS ShortName,
            MeterLocation.ShortName AS LocationName,
            Meter.ID AS MeterID,
            Line.ID AS LineID,
            MeterLine.LineName AS LineName,
            Line.VoltageKV AS Voltage,
            Event.StartTime AS InceptionTime,
    CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS CurrentDistance,
    ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS RK
FROM
    FaultSummary JOIN
    Event ON FaultSummary.EventID = Event.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
WHERE
    EventType.Name = 'Fault'


GO

ALTER PROCEDURE [dbo].[GetEventEmailRecipients]
(
    @eventID INT
)
AS BEGIN
    DECLARE @lineID INT
    DECLARE @startTime DATETIME2
    DECLARE @endTime DATETIME2
    DECLARE @timeTolerance FLOAT

    SELECT
        @lineID = LineID,
        @startTime = StartTime,
        @endTime = EndTime,
        @timeTolerance = COALESCE(Value, 0.5)
    FROM
        Event LEFT OUTER JOIN
        Setting ON Setting.Name = 'TimeTolerance'
    WHERE Event.ID = @eventID

    ; WITH cte AS
    (
        SELECT
            EmailGroupID,
            UserAccountID
        FROM EmailGroupUserAccount
        UNION
        SELECT
            EmailGroupSecurityGroup.EmailGroupID,
            UserAccount.ID
        FROM
            EmailGroupSecurityGroup JOIN
            SecurityGroupUserAccount ON EmailGroupSecurityGroup.SecurityGroupID = SecurityGroupUserAccount.SecurityGroupID JOIN
            UserAccount on SecurityGroupUserAccount.UserAccountID = UserAccount.ID
    )
    SELECT DISTINCT
        EmailGroupUserAccount.UserAccountID,
        EmailType.XSLTemplateID AS TemplateID
    FROM
        GetLineEventIDs(@lineID, @startTime, @endTime, @timeTolerance) LineEventID JOIN
        Event ON LineEventID.EventID = Event.ID JOIN
        EventType ON Event.EventTypeID = EventType.ID JOIN
        Meter ON Event.MeterID = Meter.ID LEFT OUTER JOIN
        MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID LEFT OUTER JOIN
        LineLineGroup ON LineLineGroup.LineID = Event.LineID LEFT OUTER JOIN
        EmailGroupMeterGroup ON MeterMeterGroup.MeterGroupID = EmailGroupMeterGroup.MeterGroupID LEFT OUTER JOIN
        EmailGroupLineGroup ON LineLineGroup.LineGroupID = EmailGroupLineGroup.LineGroupID JOIN
        EmailGroup ON
            EmailGroupMeterGroup.EmailGroupID = EmailGroup.ID OR
            EmailGroupLineGroup.EmailGroupID = EmailGroup.ID JOIN
        cte EmailGroupUserAccount ON EmailGroupUserAccount.EmailGroupID = EmailGroup.ID JOIN
        EmailGroupType ON EmailGroupType.EmailGroupID = EmailGroup.ID JOIN
        EmailType ON EmailGroupType.EmailTypeID = EmailType.ID JOIN
        EmailCategory ON
            EmailType.EmailCategoryID = EmailCategory.ID AND
            EmailCategory.Name = 'Event'
    WHERE
        (
            EventType.Name = 'Fault' AND
            EXISTS
            (
                SELECT *
                FROM FaultEmailCriterion
                WHERE FaultEmailCriterion.EmailGroupID = EmailGroup.ID
            )
        )
        OR
        (
            EventType.Name = 'RecloseIntoFault' AND
            EXISTS
            (
                SELECT *
                FROM FaultEmailCriterion
                WHERE
                    FaultEmailCriterion.EmailGroupID = EmailGroup.ID AND
                    FaultEmailCriterion.EmailOnReclose <> 0
            )
        )
        OR EXISTS
        (
            SELECT *
            FROM
                Disturbance JOIN
                DisturbanceSeverity ON DisturbanceSeverity.DisturbanceID = Disturbance.ID JOIN
                DisturbanceEmailCriterion ON DisturbanceSeverity.SeverityCode = DisturbanceEmailCriterion.SeverityCode
            WHERE
                Disturbance.EventID = Event.ID AND
                DisturbanceEmailCriterion.EmailGroupID = EmailGroup.ID
        )
END
GO

CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForSystem]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT @startTime = StartTime
    FROM Event
    WHERE ID = @EventID

    SELECT @prevID = COALESCE
    (
        (SELECT MAX(ID) FROM Event WHERE ID < @EventID AND StartTime = @startTime),
        (SELECT MAX(ID) FROM Event WHERE StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime))
    )

    SELECT @nextID = COALESCE
    (
        (SELECT MIN(ID) FROM Event WHERE ID > @EventID AND StartTime = @startTime),
        (SELECT MIN(ID) FROM Event WHERE StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime))
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeterLocation]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @meterLocationID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @meterLocationID = Meter.MeterLocationID
    FROM
        Event JOIN
        Meter ON Event.MeterID = Meter.ID
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                MeterLocationID = @meterLocationID
        ),
        (
            SELECT MAX(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterLocationID = @meterLocationID) AND
                MeterLocationID = @meterLocationID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                MeterLocationID = @meterLocationID
        ),
        (
            SELECT MIN(Event.ID)
            FROM
                Event JOIN
                Meter ON Event.MeterID = Meter.ID
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterLocationID = @meterLocationID) AND
                MeterLocationID = @meterLocationID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeter]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @meterID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @meterID = Event.MeterID
    FROM Event
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                MeterID = @meterID
        ),
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterID = @meterID) AND
                MeterID = @meterID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                MeterID = @meterID
        ),
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterID = @meterID) AND
                MeterID = @meterID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForLine]
    @EventID as INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startTime DATETIME2
    DECLARE @lineID INT
    DECLARE @prevID INT
    DECLARE @nextID INT

    SELECT
        @startTime = Event.StartTime,
        @lineID = Event.LineID
    FROM Event
    WHERE Event.ID = @EventID

    SELECT @prevID = COALESCE
    (
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                Event.ID < @EventID AND
                StartTime = @startTime AND
                LineID = @lineID
        ),
        (
            SELECT MAX(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND LineID = @lineID) AND
                LineID = @lineID
        )
    )

    SELECT @nextID = COALESCE
    (
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                Event.ID > @EventID AND
                StartTime = @startTime AND
                LineID = @lineID
        ),
        (
            SELECT MIN(Event.ID)
            FROM Event
            WHERE
                StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND LineID = @lineID) AND
                LineID = @lineID
        )
    )

    SELECT
        @prevID AS previd,
        @nextID AS nextid
END
GO

ALTER PROCEDURE [dbo].[selectBreakersForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, TripCoilEnergized), DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, TripCoilEnergized), DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, TripCoilEnergized), DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)'
END

DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

create table #TEMP (Name varchar(max))
insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],'
FROM #TEMP ORDER BY Name desc

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],'
FROM #TEMP ORDER BY Name desc

DROP TABLE #TEMP

SET @SQLStatement =
' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) +
' FROM (                                                                                                     ' +
'   SELECT ' + @dateStatement + '  AS Date,                                                          ' +
'          BreakerOperationType.Name,                                                                        ' +
'          COUNT(*) AS thecount                                                                              ' +
'   FROM BreakerOperation JOIN                                                                               ' +
'        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN      ' +
'        Event ON Event.ID = BreakerOperation.EventID                                                        ' +
'   WHERE MeterID in (select * from authMeters(@username)) AND                                               ' +
'         MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, '','')) AND                                ' +
'         TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate                                   ' +
'   GROUP BY ' + @groupByStatement + ', BreakerOperationType.Name                                        ' +
') as table1                                                                                                 ' +
' PIVOT(                                                                                                     ' +
'       SUM(table1.thecount)                                                                                 ' +
'       FOR table1.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')                           ' +
' ) as pvt                                                                                                   ' +
' ORDER BY Date                                                                                   '

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom


END
GO

ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Completeness > 100.0 THEN 'First'
                        WHEN 98.0 <= Completeness AND Completeness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Completeness AND Completeness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Completeness AND Completeness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Completeness AND Completeness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Completeness AND Completeness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT) AS Completeness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username))
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Correctness > 100.0 THEN 'First'
                        WHEN 98.0 <= Correctness AND Correctness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Correctness AND Correctness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Correctness AND Correctness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Correctness AND Correctness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Correctness AND Correctness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username))
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)

AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)
DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')
SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs)

SELECT thesiteid as siteid, thesitename as sitename, [5], [4], [3], [2], [1], [0]
FROM
(
    SELECT #temp.thesiteid, #temp.thesitename, SeverityCodes.SeverityCode AS SeverityCode, COALESCE(DisturbanceCount, 0) AS DisturbanceCount
    FROM
        #temp Cross JOIN
        ( Select 5 as SeverityCode UNION
          SELECT 4 as SeverityCode UNION
          SELECT 3 as SeverityCode UNION
          SELECT 2 as SeverityCode UNION
          SELECT 1 as SeverityCode UNION
          SELECT 0 as SeverityCode
        ) AS SeverityCodes LEFT OUTER JOIN
        (
            SELECT MeterID, SeverityCode, COUNT(*) AS DisturbanceCount
            FROM DisturbanceSeverity JOIN
                 Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID Join
                 Event ON Event.ID = Disturbance.EventID JOIN
				 VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
            Where ( (CAST( Event.StartTime as Date) between @EventDateFrom and @EventDateTo))
            and MeterID in (select * from authMeters(@username))
            and Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst')
			and VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC')
            GROUP BY SeverityCode, MeterID
        ) AS Disturbances ON #temp.thesiteid = Disturbances.MeterID AND Disturbances.SeverityCode = SeverityCodes.SeverityCode
) AS DisturbanceData
PIVOT
(
    SUM(DisturbanceData.DisturbanceCount)
    FOR DisturbanceData.SeverityCode IN ([5], [4], [3], [2], [1], [0])
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDateRange]

    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, Disturbance.StartTime), DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, Disturbance.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, Disturbance.StartTime), DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)'
END

DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

create table #TEMP (Name varchar(max))
insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @PivotColumns = @PivotColumns + '[0]'

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '0') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @ReturnColumns = @ReturnColumns + 'COALESCE([0],0) as [0]'

SET @SQLStatement =
N' 
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterID
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT DisturbanceDate as thedate, ' + @ReturnColumns + '
 FROM (																		 
	SELECT                                                                       
		' + @dateStatement + ' AS DisturbanceDate,                	 
		SeverityCode,															 
		COUNT(*) AS DisturbanceCount											 
	FROM																		 
		DisturbanceSeverity JOIN												 
		Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN	 
		Event ON Event.ID = Disturbance.EventID JOIN							 
		Phase ON Disturbance.PhaseID = Phase.ID	JOIN
		VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID								 
	WHERE																		 
		(																		 
			@MeterID = ''0'' OR													 
			MeterID IN (SELECT * FROM String_To_Int_Table(@ids, '',''))	     
		) AND																	 
		MeterID IN (SELECT * FROM authMeters(@user)) AND					 
		Phase.Name = ''Worst'' AND
		VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND										 
		Disturbance.StartTime BETWEEN @start AND @end AND				 
		Disturbance.StartTime <> @endDate									 
	GROUP BY ' + @groupByStatement + ', SeverityCode 					 
	) As DisturbanceDate														 
 PIVOT(
		SUM(DisturbanceDate.DisturbanceCount) 
		FOR DisturbanceDate.SeverityCode IN(' + @PivotColumns + ')
 ) as pvt
 ORDER BY DisturbanceDate '
print @sqlstatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIMe, @endDate DATEtime, @EventDateFrom DATETIME, @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom, @voltageEnvelope = @voltageEnvelope

END
GO

ALTER PROCEDURE [dbo].[selectEventsForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SET @SQLStatement =
' 
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterID
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM (
		SELECT ' + @dateStatement + ' as Date, COUNT(*) AS EventCount, EventType.Name as Name
		FROM Event JOIN
		EventType ON Event.EventTypeID = EventType.ID 
       WHERE 
			MeterID in (select * from authMeters(@user)) AND 
			MeterID IN (SELECT * FROM String_To_Int_Table( @ids,  '','')) AND 
			 StartTime >= @start AND StartTime < @end  
       GROUP BY ' + @groupByStatement + ', EventType.Name 
       ) as ed 
 PIVOT( 
		SUM(ed.EventCount)
		FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') 
 ) as pvt 
 ORDER BY Date '

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom


END
GO

ALTER PROCEDURE [dbo].[selectFaultsForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, Event.StartTime), DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, Event.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, Event.StartTime), DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SET @SQLStatement =
' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) +
' FROM ( ' +
'       SELECT ' + @dateStatement + ' AS Date, Line.VoltageKV, COUNT(*) AS thecount ' +
'       FROM Event JOIN '+
'       EventType ON Event.EventTypeID = EventType.ID JOIN ' +
'       Line ON Event.LineID = Line.ID ' +
'       WHERE EventType.Name = ''Fault'' AND ' +
'       MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '','')) AND Event.StartTime >= @startDate AND Event.StartTime < @endDate  ' +
'       GROUP BY ' + @groupByStatement + ', EventType.Name, Line.VoltageKV ' +
'       ) as eventtable ' +
' PIVOT( ' +
'       SUM(eventtable.thecount) ' +
'       FOR eventtable.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' +
' ) as pvt ' +
' ORDER BY Date '

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom
END
GO

CREATE PROCEDURE [dbo].[selectExtensionsForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @MiddleStatment NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT '+@dateStatement+' as date,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY '+ @dateStatement + ' UNION'

FROM (Select * FROM EASExtension) AS t

SELECT * INTO #temp
FROM EVENT
WHERE   StartTime Between @startDate AND @endDate AND
        MeterID in (select * from authMeters(@username)) AND
        MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  ','))

CREATE INDEX tempIndex ON #temp (MeterID)

SET @SQLStatement =
'
SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM (
        SELECT * FROM (
        ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as innertable
       ) as ed
 PIVOT(
        SUM(ed.EventCount)
        FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
 ) as pvt
 ORDER BY Date

 DROP TABLE #temp
 '

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsBreakers]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @startDate DATETIME = @EventDateFrom
    DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


    IF @context = 'day'
    BEGIN
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @CountColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    create table #TEMP (Name varchar(max))
    insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(20)), '') + '], 0) + '
    FROM #TEMP ORDER BY Name desc

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    DROP TABLE #TEMP


    SET @SQLStatement =
    ' SELECT Meter.ID,
             Meter.Name,
             MeterLocation.Longitude,
             MeterLocation.Latitude,
             ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
    FROM
        Meter JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
        (
            SELECT MeterID,
                   COUNT(*) AS EventCount,
                   BreakerOperationType.Name as OperationType
            FROM BreakerOperation JOIN
                 Event ON BreakerOperation.EventID = Event.ID JOIN
                 EventType ON Event.EventTypeID = EventType.ID JOIN
                 BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID
            WHERE
                 TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate
            GROUP BY Event.MeterID, BreakerOperationType.Name
           ) as ed
           PIVOT(
                 SUM(ed.EventCount)
                 FOR ed.OperationType IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
           ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID in (select * from authMeters(@username)) AND
        Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds,  '',''))

    ORDER BY Meter.Name'

    print @SQLStatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsCompleteness]
    @EventDateFrom DateTime,
    @EventDateTo DateTime,
    @meterIds AS varchar(max)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT
        Meter.ID,
        Meter.Name,
        MeterLocation.Longitude,
        MeterLocation.Latitude,
        (
            SELECT CAST(COALESCE(CAST(SUM(goodPoints) AS FLOAT) / NULLIF(CAST(SUM(expectedPoints) AS FLOAT), 0) * 100 , 0) AS INT)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS Count,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.ExpectedPoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS ExpectedPoints,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.GoodPoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS GoodPoints,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.LatchedPoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS LatchedPoints,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.UnreasonablePoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS UnreasonablePoints,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.NoncongruentPoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS NoncongruentPoints,
        (
            SELECT COALESCE(SUM(MeterDataQualitySummary.DuplicatePoints), 0)
            FROM MeterDataQualitySummary
            WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate
        ) AS DuplicatePoints
        FROM
            Meter JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
        WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
        ORDER BY Meter.Name
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsCorrectness]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT 
        Meter.ID, 
        Meter.Name, 
        MeterLocation.Longitude, 
        MeterLocation.Latitude, 
		COALESCE(CAST(CAST(SUM(MeterDataQualitySummary.GoodPoints) AS FLOAT) / NULLIF(CAST(SUM(MeterDataQualitySummary.ExpectedPoints) AS FLOAT), 0) * 100 AS INT),0) AS Count,
		COALESCE(SUM(MeterDataQualitySummary.ExpectedPoints) , 0) AS ExpectedPoints,
		COALESCE(SUM(MeterDataQualitySummary.GoodPoints),0) AS GoodPoints,
		COALESCE(SUM(MeterDataQualitySummary.LatchedPoints),0) AS LatchedPoints,
		COALESCE(SUM(MeterDataQualitySummary.UnreasonablePoints),0) AS UnreasonablePoints,
		COALESCE(SUM(MeterDataQualitySummary.NoncongruentPoints),0) AS NoncongruentPoints,
		COALESCE(SUM(MeterDataQualitySummary.DuplicatePoints),0) AS DuplicatePoints
        FROM
            Meter JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT JOIN
			( SELECT * FROM MeterDataQualitySummary WHERE [Date] BETWEEN @startDate AND @endDate) as MeterDataQualitySummary ON MeterDataQualitySummary.MeterID = Meter.ID
		WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) 
		GROUP BY Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude
        ORDER BY Meter.Name
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsDisturbances]

    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom 
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
	SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
	SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
	SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
	SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''


create table #TEMP (Name varchar(max))
insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @PivotColumns = @PivotColumns + '[0]'

SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) + ' 
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @CountColumns = @CountColumns + 'COALESCE([0], 0) '


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
FROM #TEMP WHERE Name != 0ORDER BY Name desc
SET @ReturnColumns = @ReturnColumns + 'COALESCE([0], 0) as [0]'

DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

DROP TABLE #TEMP

SET @SQLStatement = N'
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterIds
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT Meter.ID, 
		 Meter.Name,
		 MeterLocation.Longitude,
		 MeterLocation.Latitude,
		 ' + @CountColumns +' as Count,
		 ' + @ReturnColumns + '		  
FROM 
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
	(
		SELECT MeterID, 
			   COUNT(*) AS EventCount, 
			   SeverityCode			   
		FROM Event JOIN
			 Disturbance ON Event.ID = Disturbance.EventID JOIN
			 Phase ON Phase.ID = Disturbance.PhaseID LEFT JOIN
			 DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
			 VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID	
        WHERE 
			 Phase.Name = ''Worst'' AND 
			 Disturbance.StartTime >= @start AND Disturbance.StartTime < @end AND
			 VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'')										 
        GROUP BY Event.MeterID, SeverityCode 
       ) as ed 
	   PIVOT( 
	   		 SUM(ed.EventCount)
	   		 FOR ed.SeverityCode IN(' + @PivotColumns + ') 
	   ) as pvt On pvt.MeterID = meter.ID
	WHERE 
		Meter.ID in (select * from authMeters(@user)) AND 
		Meter.ID IN (SELECT * FROM String_To_Int_Table( @ids,  '',''))  

Order By Name '

print @SqlStatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME , @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsEvents]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.Name + '], 0) + '
FROM (Select Name FROM EventType) AS t


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SET @SQLStatement =
' 
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterIds
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT Meter.ID, 
		 Meter.Name,
		 MeterLocation.Longitude,
		 MeterLocation.Latitude,
		 ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
		 ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM 
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
	(
		SELECT MeterID, 
			   COUNT(*) AS EventCount, 
			   EventType.Name as Name
			   
		FROM Event JOIN
			 EventType ON Event.EventTypeID = EventType.ID 
        WHERE 
			 StartTime >= @start AND StartTime < @end  
        GROUP BY Event.MeterID, EventType.Name 
       ) as ed 
	   PIVOT( 
	   		 SUM(ed.EventCount)
	   		 FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') 
	   ) as pvt On pvt.MeterID = meter.ID
WHERE 
	Meter.ID in (select * from authMeters(@user)) AND 
	Meter.ID IN (SELECT * FROM String_To_Int_Table( @ids,  '',''))  

ORDER BY Meter.Name'

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsFaults]
    @EventDateFrom DateTime,
    @EventDateTo DateTime,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) + '
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t


SET @SQLStatement =
' SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    (
        SELECT MeterID,
               COUNT(*) AS EventCount,
               VoltageKV
        FROM Event JOIN
             EventType ON Event.EventTypeID = EventType.ID JOIN
             Line ON Event.LineID = Line.ID
        WHERE
             StartTime >= @startDate AND StartTime < @endDate AND
             EventType.Name = ''Fault''
        GROUP BY Event.MeterID, VoltageKV
       ) as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID in (select * from authMeters(@username)) AND
        Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds,  '',''))

ORDER BY Meter.Name'

print @SQLStatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsMaximumSwell]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT
        Meter.ID,
        Meter.Name,
        MeterLocation.Longitude,
        MeterLocation.Latitude,
        CAST(COALESCE((
            SELECT (10.0/9.0) * ((MAX([PerUnitMagnitude]) - 1.1)) * 100
            FROM
                Disturbance  JOIN
                Event ON Disturbance.EventID = Event.ID JOIN
                EventType ON EventType.ID = Event.EventTypeID AND EventType.Name = 'Swell'
            WHERE
                CAST(Event.StartTime AS DATE) BETWEEN @startDate AND @endDate AND
                Event.MeterID = Meter.ID AND
                PerUnitMagnitude IS NOT NULL AND
                PerUnitMagnitude >= 1.1
        ), 0) AS INT) AS Count
    FROM
        Meter JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
        WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
    ORDER BY Meter.Name

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsMinimumSags]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT
        Meter.ID,
        Meter.Name,
        MeterLocation.Longitude,
        MeterLocation.Latitude,
        CAST(COALESCE((
            SELECT (10.0/8.0) * (0.9 - (MIN(PerUnitMagnitude))) * 100
            FROM
                Disturbance JOIN
                Event ON Disturbance.EventID = Event.ID JOIN
                EventType ON EventType.ID = Event.EventTypeID AND EventType.Name = 'Sag'
            WHERE
                CAST(Event.StartTime AS DATE) BETWEEN @startDate AND @endDate AND
                Event.MeterID = Meter.ID AND
                PerUnitMagnitude IS NOT NULL AND PerUnitMagnitude <= 0.9
        ), 0) AS INT) AS Count
    FROM
        Meter JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
        WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
    ORDER BY Meter.Name

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsTrending]
    @EventDateFrom DateTime,
    @EventDateTo DateTime,
    @meterIds AS varchar(max)
AS
BEGIN

    SET NOCOUNT ON;

        declare @thedatefrom as Date;
        declare @thedateto as Date;

        set @thedatefrom = CAST(@EventDateFrom as Date);
        set @thedateto = CAST(@EventDateTo as Date);

        SELECT
            [dbo].[Meter].[ID],
            [dbo].[Meter].[Name],
            [dbo].[MeterLocation].[Longitude],
            [dbo].[MeterLocation].[Latitude],
            COALESCE(Alarm, 0) as Alarm,
            COALESCE(Offnormal, 0) As Offnormal,
            COALESCE(AlarmCount, 0) As AlarmCount
        FROM
            Meter JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
            (
                SELECT
                    MeterID,
                    Alarm + Offnormal as AlarmCount,
                    Alarm,
                    Offnormal
                FROM
                    (
                        SELECT
                            Channel.MeterID,
                            AlarmType.Name
                        FROM
                            AlarmType JOIN
                            ChannelAlarmSummary ON ChannelAlarmSummary.AlarmTypeID = AlarmType.ID JOIN
                            Channel ON Channel.ID = ChannelAlarmSummary.ChannelID
                        WHERE ChannelAlarmSummary.Date BETWEEN @thedatefrom AND @thedateto

                    ) AS AlarmCodes
                    PIVOT
                    (
                        COUNT(Name)
                        FOR Name IN (Alarm, Offnormal)
                    ) AS PivotTable
        ) AS AlarmCount ON AlarmCount.MeterID = Meter.ID
        WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
       ORDER BY Meter.Name

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsTrendingData]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @colorScaleName VARCHAR(200),
    @meterIds AS varchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @thedatefrom AS DATE
    DECLARE @thedateto AS DATE

    SET @thedatefrom = CAST(@EventDateFrom AS DATE)
    SET @thedateto = CAST(@EventDateTo AS DATE)

    SELECT
        Meter.ID,
        Meter.Name,
        Data.Minimum AS Minimum,
        Data.Maximum AS Maximum,
        Data.Average AS Average,
        MeterLocation.Longitude,
        MeterLocation.Latitude
    FROM
        Meter LEFT OUTER JOIN
        (
            SELECT
                ContourChannel.MeterID AS MID,
                MIN(Minimum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Minimum,
                MAX(Maximum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Maximum,
                AVG(Average/COALESCE(ContourChannel.PerUnitValue, 1)) AS Average
            FROM
                ContourChannel JOIN
                DailyTrendingSummary ON  DailyTrendingSummary.ChannelID = ContourChannel.ChannelID
            WHERE Date >= @thedatefrom AND Date <= @thedateto AND ContourColorScaleName = @colorScaleName
            GROUP BY ContourChannel.MeterID
        ) AS Data ON Data.MID = Meter.ID JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
        WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ','))
    ORDER BY Meter.Name
END
GO

CREATE PROCEDURE [dbo].[selectMeterLocationsExtensions]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''
DECLARE @MiddleStatment NVARCHAR(MAX) = N''

SELECT * INTO #temp
FROM EVENT
WHERE   StartTime Between @startDate AND @endDate AND
        MeterID in (select * from authMeters(@username)) AND
        MeterID IN (SELECT * FROM String_To_Int_Table( @meterIds,  ','))

CREATE INDEX tempIndex ON #temp (MeterID)

CREATE TABLE #easTable( MeterID int, ServiceName varchar(max), EventCount int);

SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.ServiceName + '], 0) + '
FROM (Select ServiceName FROM EASExtension) AS t


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

DECLARE @serviceName as varchar(max);
DECLARE @hasResultFunction as varchar(max);

DECLARE aCursor CURSOR FOR
SELECT ServiceName, HasResultFunction FROM EASExtension
OPEN aCursor;
FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction;
WHILE @@FETCH_STATUS = 0
   BEGIN
    DECLARE @Sql nvarchar(max) = N'
            INSERT INTO #easTable
            SELECT MeterID,
                '''+ @serviceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + @hasResultFunction + '(ID) != ''''
            GROUP BY MeterID'
    exec sp_executesql @sql
    FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction;
   END;
CLOSE aCursor;
DEALLOCATE aCursor;


SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

FROM (Select * FROM EASExtension) AS t

SET @SQLStatement =
'
SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    #easTable as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
WHERE
    Meter.ID in (select * from authMeters(@username)) AND
    Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds,  '',''))

ORDER BY Meter.Name

DROP TABLE #temp
DROP TABLE #easTable
'
--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END

GO

ALTER PROCEDURE [dbo].[selectSiteLinesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @startDate DATETIME = @EventDate 
	DECLARE @endDate DATETIME

    IF @context = '180d'
    BEGIN
		SET @startDate = DATEADD(HOUR, -180, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '90d'
    BEGIN
		SET @startDate = DATEADD(DAY, -90, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '30d'
    BEGIN
		SET @startDate = DATEADD(DAY, -30, @EventDate)
		SET @endDate = @EventDate
    END

    IF @context = '7d'
    BEGIN
		SET @startDate = DATEADD(DAY, -7, @EventDate)
		SET @endDate = @EventDate
    END

	IF @context = '24h'
	BEGIN
		SET @startDate = DATEADD(HOUR, -24, @EventDate)
		SET @endDate = @EventDate
	END

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END


	DECLARE @simStartDate DATETIME = DATEADD(SECOND, -5, @startDate)
	DECLARE @simEndDate DATETIME = DATEADD(SECOND, 5, @endDate)
	print @simStartDate
	print @simEndDate
    DECLARE @localEventDate DATE = CAST(@EventDate AS DATE)
    DECLARE @localMeterID INT = CAST(@MeterID AS INT)
	DECLARE @timeWindow int = (SELECT Value FROM DashSettings WHERE Name = 'System.TimeWindow')

    ; WITH cte AS
    (
        SELECT
            Event.LineID AS thelineid, 
            Event.ID AS theeventid, 
            EventType.Name AS theeventtype,
            CAST(Event.StartTime AS VARCHAR(26)) AS theinceptiontime,
            MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
            Line.VoltageKV AS voltage,
            COALESCE(FaultSummary.FaultType, Phase.Name, '') AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE COALESCE(CAST(CAST(FaultSummary.Distance AS DECIMAL(16, 4)) AS NVARCHAR(19)), '') END AS thecurrentdistance,
            dbo.EventHasImpactedComponents(Event.ID) AS pqiexists,
            Event.StartTime,
            CASE EventType.Name
                WHEN 'Sag' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Interruption' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Swell' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude DESC, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception)
                WHEN 'Fault' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY IsSelectedAlgorithm DESC, IsSuppressed, IsValid DESC, Inception)
                ELSE ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Event.ID)
            END AS RowPriority,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.StartTime BETWEEN DateAdd(SECOND, -5, Event.StartTime) and  DateAdd(SECOND, 5, Event.StartTime)) as SimultaneousCount,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name = 'Sag' OR Name = 'Fault') AND EventCount.StartTime BETWEEN DateAdd(SECOND, -@timeWindow, Event.StartTime) and  DateAdd(SECOND, @timeWindow, Event.StartTime)) as SimultaneousFAndSCount,
			(SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.LineID = Event.LineID AND EventCount.StartTime BETWEEN DateAdd(Day, -60, Event.StartTime) and  Event.StartTime) as SixtyDayCount,
			Event.UpdatedBy,
			(SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as Note
        FROM
            Event JOIN
            EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
            Disturbance ON Disturbance.EventID = Event.ID LEFT OUTER JOIN
            FaultSummary ON FaultSummary.EventID = Event.ID  LEFT OUTER JOIN
            Phase ON Disturbance.PhaseID = Phase.ID JOIN
            Meter ON Meter.ID = @MeterID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID
        WHERE
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND 
            Event.MeterID = @localMeterID AND
            (Phase.ID IS NULL OR Phase.Name <> 'Worst')
    )
    SELECT
        thelineid,
        theeventid,
        theeventtype,
        theinceptiontime,
        thelinename,
        voltage,
        thefaulttype,
        thecurrentdistance,
        pqiexists,
		SimultaneousCount,
		SimultaneousFAndSCount,
		SixtyDayCount,
		UpdatedBy,
		Note
    INTO #temp
    FROM cte
    WHERE RowPriority = 1
    ORDER BY StartTime

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = COALESCE(@sql + ',dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName, 'dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName)
    FROM EASExtension

	DECLARE @serviceList NVARCHAR(MAX)
	SELECT @serviceList = COALESCE(@serviceList + ',' + ServiceName, ServiceName)
	FROM EASExtension
	Set @serviceList = '''' + @serviceList + ''''
	
	
    SET @sql = COALESCE('SELECT *,' + @sql + ', '+ @ServiceList +'as ServiceList FROM #temp', 'SELECT *, '''' AS ServiceList FROM #temp')
    print @sql
	EXEC sp_executesql @sql

    DROP TABLE #temp
	
END
GO

ALTER PROCEDURE [dbo].[selectSiteLinesDisturbanceDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @worstPhaseID INT = (SELECT ID FROM Phase WHERE Name = 'Worst')
	DECLARE @startDate DATETIME = @EventDate 
	DECLARE @endDate DATETIME


    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

	DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

	SELECT 
		Event.LineID AS thelineid, 
		Event.ID AS theeventid, 
		Disturbance.ID as disturbanceid,
		EventType.Name AS disturbancetype,
		Phase.Name AS phase,
        CASE Disturbance.PerUnitMagnitude
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(Disturbance.PerUnitMagnitude AS VARCHAR(8))
        END AS magnitude,
        CASE Disturbance.DurationSeconds
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(CONVERT(DECIMAL(10,3), Disturbance.DurationSeconds) AS VARCHAR(14))
        END AS duration,
		CAST(Disturbance.StartTime AS VARCHAR(26)) AS theinceptiontime,
        dbo.DateDiffTicks('1970-01-01', Disturbance.StartTime) / 10000.0 AS startmillis,
        dbo.DateDiffTicks('1970-01-01', Disturbance.EndTime) / 10000.0 AS endmillis,
		DisturbanceSeverity.SeverityCode,
		MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
		Line.VoltageKV AS voltage,
	    (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as notes
	FROM
		Event JOIN
		Disturbance ON Disturbance.EventID = Event.ID JOIN
        Disturbance WorstDisturbance ON
            Disturbance.EventID = WorstDisturbance.EventID AND
            Disturbance.PerUnitMagnitude = WorstDisturbance.PerUnitMagnitude AND
            Disturbance.DurationSeconds = WorstDisturbance.DurationSeconds JOIN
		EventType ON Disturbance.EventTypeID = EventType.ID JOIN
		Phase ON Disturbance.PhaseID = Phase.ID JOIN
		DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
		Meter ON Meter.ID = @MeterID JOIN
		Line ON Event.LineID = Line.ID JOIN
		MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID JOIN
		VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID	
	WHERE
		Event.StartTime >= @startDate AND Event.StartTime < @endDate AND 
		Event.MeterID = @MeterID AND
        WorstDisturbance.PhaseID = @worstPhaseID AND
        Disturbance.PhaseID <> @worstPhaseID AND
		VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC')
	ORDER BY
		Event.StartTime ASC

END
GO

CREATE PROCEDURE [dbo].[selectSiteLinesExtensionDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DATETIME
    DECLARE @endDate DATETIME

    IF @context = '180d'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(Day, 180, @startDate)
    END

	IF @context = '90d'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(Day, 90, @startDate)
    END

    IF @context = 'month'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(Day, 30, @startDate)
    END

    IF @context = 'week'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 7, @startDate)
    END

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    DECLARE @localEventDate DATE = CAST(@EventDate AS DATE)
    DECLARE @localMeterID INT = CAST(@MeterID AS INT)

    DECLARE @MiddleStatment NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    SELECT @MiddleStatment = @MiddleStatment +  '
                SELECT  Event.ID as EventID,
                        Event.StartTime as StartTime,
                        '''+ t.ServiceName + ''' as ServiceType,
                        MeterLine.LineName + '' '' + [Line].[AssetKey] AS LineName,
                        Line.VoltageKV AS Voltage,
						CAST(dbo.'+ t.HasResultFunction+'(Event.ID) as varchar(max)) as Confidence
                FROM    #temp as Event JOIN
                        Meter ON Meter.ID = Event.MeterID JOIN
                        Line ON Event.LineID = Line.ID JOIN
                        MeterLine ON MeterLine.MeterID = Event.MeterID AND MeterLine.LineID = Line.ID
                WHERE   dbo.' + t.HasResultFunction +'(Event.ID) != '''' UNION'

    FROM (Select * FROM EASExtension) AS t

    SET @MiddleStatment = SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION'))

    SET @SQLStatement =
        '
        SELECT * INTO #temp
        FROM EVENT
        WHERE   StartTime Between @startDate AND @endDate AND
                MeterID = @localMeterID

        ' + @MiddleStatment + '

         DROP TABLE #temp
 '

    exec sp_executesql @SQLStatement, N'@localMeterID int, @startDate DATETIME, @endDate DATETIME ', @localMeterID = @localMeterID, @startDate = @startDate, @endDate = @endDate

END

GO

ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate]
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime
    DECLARE @endDate DateTime

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    SELECT
        Meter.ID AS meterid,
        Event.ID AS theeventid,
        EventType.Name AS eventtype,
        BreakerOperation.ID AS breakeroperationid,
        CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized,
        BreakerOperation.BreakerNumber AS breakernumber,
        MeterLine.LineName AS linename,
        Phase.Name AS phasename,
        CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing,
        CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming,
        BreakerOperation.BreakerSpeed AS speed,
        BreakerOperation.StatusBitChatter AS chatter,
        BreakerOperation.DcOffsetDetected AS dcoffset,
        BreakerOperationType.Name AS operationtype,
		(SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount
    FROM
        BreakerOperation JOIN
        Event ON BreakerOperation.EventID = Event.ID JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        Line ON Line.ID = Event.LineID JOIN
        MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
        Phase ON BreakerOperation.PhaseID = Phase.ID
    WHERE
        TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND
        Meter.ID IN (SELECT * FROM  dbo.String_to_int_table(@MeterID, ',')) AND
        Meter.ID IN (select * from authMeters(@username))
END
GO

ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @startDate DateTime = @EventDate
	DECLARE @endDate DateTime

	if @context = 'day'
		SET @endDate = DATEADD(DAY, 1, @startDate);
	if @context = 'hour'
		SET @endDate = DATEADD(HOUR, 1, @startDate);
	if @context = 'minute'
		SET @endDate = DATEADD(MINUTE, 1, @startDate);
	if @context = 'second'
		SET @endDate = DATEADD(SECOND, 1, @startDate);

	DECLARE @PivotColumns NVARCHAR(MAX) = N''
	DECLARE @ReturnColumns NVARCHAR(MAX) = N''
	DECLARE @SQLStatement NVARCHAR(MAX) = N''

	create table #TEMP (Name varchar(max))
	insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

	SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
	FROM #TEMP ORDER BY Name desc

	SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
	FROM #TEMP ORDER BY Name desc
	DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

	SET @SQLStatement =
	' SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) 
			 EventID, 
			 MeterID, 
			 Site, 
			 ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
	 FROM ( 
		SELECT 
			Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site 
		FROM 
			Disturbance JOIN 
			DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
			Event ON Disturbance.EventID = Event.ID JOIN
			EventType ON Event.EventTypeID = EventType.ID JOIN
			Meter ON Event.MeterID = Meter.ID JOIN
			Phase ON Phase.ID = Disturbance.PhaseID JOIN
			VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID	
		WHERE
			Phase.Name = ''Worst'' AND 
    		MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '','')) AND 
			VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND										 
			Event.StartTime >= @startDate AND Event.StartTime < @endDate  
	    GROUP BY Event.MeterID,Meter.Name,SeverityCode
	       ) as ed 
	 PIVOT( 
			SUM(ed.EventCount)
			FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') 
	 ) as pvt
	 ORDER BY MeterID '

	print @startDate
	print @endDate
	print @sqlstatement
	exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope
END

GO

ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @startDate DateTime = @EventDate
	DECLARE @endDate DateTime

	if @context = 'day'
		SET @endDate = DATEADD(DAY, 1, @startDate);
	if @context = 'hour'
		SET @endDate = DATEADD(HOUR, 1, @startDate);
	if @context = 'minute'
		SET @endDate = DATEADD(MINUTE, 1, @startDate);
	if @context = 'second'
		SET @endDate = DATEADD(SECOND, 1, @startDate);

	DECLARE @PivotColumns NVARCHAR(MAX) = N''
	DECLARE @ReturnColumns NVARCHAR(MAX) = N''
	DECLARE @SQLStatement NVARCHAR(MAX) = N''

	SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' 
	FROM (Select Name FROM EventType) AS t

	SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' 
	FROM (Select Name FROM EventType) AS t

	SET @SQLStatement =
	' SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) 
			 EventID, 
			 MeterID, 
			 Site, 
			 ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
	 FROM ( 
		SELECT 
			Event.MeterID, COUNT(*) AS EventCount, EventType.Name, Meter.Name as Site 
			FROM Event JOIN
			EventType ON Event.EventTypeID = EventType.ID JOIN
			Meter ON Event.MeterID = Meter.ID
	       WHERE 
    			MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '','')) AND 
			 StartTime >= @startDate AND StartTime < @endDate  
	       GROUP BY Event.MeterID,Meter.Name,EventType.Name 
	       ) as ed 
	 PIVOT( 
			SUM(ed.EventCount)
			FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') 
	 ) as pvt
	 ORDER BY MeterID '

	print @startDate
	print @endDate
	print @sqlstatement
	exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate
END
GO

ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);


    ; WITH FaultDetail AS
    (
        SELECT
            FaultSummary.ID AS thefaultid,
            Meter.Name AS thesite,
            Meter.ShortName AS theshortsite,
            MeterLocation.ShortName AS locationname,
            Meter.ID AS themeterid,
            Line.ID AS thelineid,
            Event.ID AS theeventid,
            MeterLine.LineName AS thelinename,
            Line.VoltageKV AS voltage,
            CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime,
            FaultSummary.FaultType AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance,
            (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount,
            ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk
        FROM
            FaultSummary JOIN
            Event ON FaultSummary.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
        WHERE
            EventType.Name = 'Fault' AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
            Meter.ID IN (SELECT * FROM  dbo.String_to_int_table(@MeterID, ',')) AND
            Meter.ID IN (select * from authMeters(@username))
    )
    SELECT *
    FROM FaultDetail
    WHERE rk = 1
END
GO

ALTER PROCEDURE [dbo].[selectSitesTrendingDataDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate DateTime2,
    @colorScaleName VARCHAR(200),
    @MeterID AS nvarchar(MAX),
    @username AS nvarchar(4000)

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE  @MeterIDs TABLE (ID int);
    DECLARE  @ChannelID AS nvarchar(MAX);
    DECLARE  @Date as DateTime2;
    SET  @Date = CAST(@EventDate AS DATE)

    -- Create MeterIDs Table
    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') where Value in (select * from authMeters(@username));

    -- Trending Data
    SELECT
        Meter.ID as meterid,
        Meter.Name as Name,
        Channel.ID as channelid,
        DailyTrendingSummary.Date as date,
        MIN(Minimum/COALESCE(Channel.PerUnitValue,1)) as Minimum,
        MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum,
        AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average,
        MeasurementCharacteristic.Name as characteristic,
        MeasurementType.Name as measurementtype,
        Phase.Name as phasename
    FROM
        DailyTrendingSummary JOIN
        Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN
        Meter ON Meter.ID = Channel.MeterID JOIN
        MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
        MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
        Phase ON Channel.PhaseID = Phase.ID
    WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = @colorScaleName) AND Date = @Date
    GROUP BY Date, Meter.ID, Meter.Name, MeasurementCharacteristic.Name, MeasurementType.Name, Phase.Name, Channel.ID
    ORDER BY Date
END
GO

CREATE PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''
    DECLARE @MiddleStatment NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

    FROM (Select * FROM EASExtension) AS t

    SET @SQLStatement =
    '
    SELECT * INTO #temp
    FROM EVENT
    WHERE   StartTime Between @startDate AND @endDate AND
            MeterID in (select * from authMeters(@username)) AND
            MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '',''))

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID,
             Meter.ID as MeterID,
             Meter.Name as Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM Meter Join
        (
            ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + '
         ) as ed
         PIVOT(
                SUM(ed.EventCount)
                FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
         ) as pvt ON pvt.MeterID = Meter.ID
     WHERE
        Meter.ID in (select * from authMeters(@username)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '',''))

     ORDER BY MeterID
     DROP Table #temp
     '

    --print @startDate
    --print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate

END
GO

ALTER PROCEDURE [dbo].[selectTrendingDataByChannelByDate]
    @StartDate DateTime2,
    @EndDate DateTime2,
    @colorScale AS varchar(200),
    @MeterID AS nvarchar(MAX),
    @username AS nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE  @MeterIDs TABLE (ID int);
    DECLARE  @ChannelID AS nvarchar(MAX);
    DECLARE  @BeginDate DateTime2;
    DECLARE  @StopDate DateTime2;

    SET @BeginDate = @StartDate;
    SET @StopDate = @EndDate;

    -- Create MeterIDs Table
    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') where Value in (select * from authMeters(@username));

    -- Trending Data
    SELECT
        Date,
        MIN(Minimum/COALESCE(Channel.PerUnitValue, 1)) as Minimum,
        MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum,
        AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average
    FROM
        DailyTrendingSummary JOIN
        Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN
        Meter ON Meter.ID = Channel.MeterID
    WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourChannel.ContourColorScaleName = @colorScale) AND Date >= @BeginDate AND Date <= @StopDate
    GROUP BY Date
    ORDER BY Date
END
GO

ALTER PROCEDURE [dbo].[selectTrendingForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT AlarmDate as thedate, COALESCE(OffNormal,0) as Offnormal, COALESCE(Alarm,0) as Alarm
FROM(
    SELECT Date AS AlarmDate, AlarmType.Name, SUM(AlarmPoints) as AlarmPoints
    FROM ChannelAlarmSummary JOIN
         Channel ON ChannelAlarmSummary.ChannelID = Channel.ID JOIN
         AlarmType ON AlarmType.ID = ChannelAlarmSummary.AlarmTypeID
    WHERE MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND Date >= @startDate AND Date < @endDate
    GROUP BY Date, AlarmType.Name
) AS table1
PIVOT(
    SUM(table1.AlarmPoints)
    FOR table1.Name IN(Alarm, OffNormal)
) as pvt

END
GO

CREATE PROCEDURE [dbo].[GetSiteSummaries]
    -- Add the parameters for the stored procedure here
    @startDate as DateTime, 
    @endDate as DateTime, 
    @filterID as int,
	@Page int,
	@RecsPerPage int,
	@orderBy varchar(MAX),
	@filterString varchar(MAX)
AS
BEGIN
SET NOCOUNT ON


DECLARE @sql NVARCHAR(MAX)

SET @sql = '
DECLARE @Page int, @RecsPerPage int
SET @Page = ' + CAST(@Page as varchar) + '
SET @RecsPerPage = ' + CAST(@RecsPerPage as varchar) + '
-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage;
SELECT @LastRec = (@Page * @RecsPerPage + 1);
DECLARE @EventDateFrom DATE = CAST( '''+ CAST(@startDate as varchar(100)) +''' AS DATE)
DECLARE @EventDateTO DATE = CAST( '''+ CAST(@endDate as varchar(100)) +''' AS DATE)
DECLARE @filterID int = ' + CAST(@filterID as varchar(4)) + ';
WITH TempResult as
(
SELECT 
  '
  +
    ' Meter.Name AS MeterID, 
	   (SELECT SUM(100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT)) / DATEDIFF(day, @EventDateFrom, @EventDateTo) FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND MeterDataQualitySummary.Date BETWEEN @EventDateFrom AND @EventDateTo ) as Completeness,
	   (SELECT SUM(100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT)) / DATEDIFF(day, @EventDateFrom,  @EventDateTo) FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND MeterDataQualitySummary.Date BETWEEN @EventDateFrom AND @EventDateTo) as Correctness,
	   (SELECT COUNT(Event.ID) FROM Event WHERE MeterID = Meter.ID AND StartTime BETWEEN @EventDateFrom AND @EventDateTo AND EventTypeID IN (SELECT * FROM String_To_Int_Table((SELECT EventTypes FROM WorkbenchFilter Where ID = @filterID), '',''))) AS [Events],
       (SELECT COUNT(Disturbance.ID) FROM Disturbance JOIN Event ON Disturbance.EventID = Event.ID WHERE MeterID = Meter.ID AND Event.StartTime BETWEEN @EventDateTo AND @EventDateFrom) AS Disturbances, 
       (SELECT COUNT(ID) FROM FaultView WHERE MeterID = Meter.ID AND InceptionTime BETWEEN @EventDateFrom AND @EventDateTo AND RK = 1) AS Faults,
       (SELECT Max(Maximum) FROM DailyTrendingSummary WHERE ChannelID IN (SELECT ID FROM Channel WHERE MeterID = Meter.ID AND MeasurementTypeID = 2) AND Date BETWEEN @EventDateFrom AND @EventDateTo) AS MaxCurrent,
       (SELECT Min(Minimum) FROM DailyTrendingSummary WHERE ChannelID IN (SELECT ID FROM Channel WHERE MeterID = Meter.ID AND MeasurementTypeID = 1) AND Date BETWEEN @EventDateFrom AND @EventDateTo) AS MinVoltage 
FROM Meter 
WHERE Meter.ID IN (Select * FROM String_To_Int_Table((Select Meters FROM WorkbenchFilter WHERE ID = @filterID), '','')) AND
	  Meter.Name LIKE ''%'+@filterString+'%''
), 
TempResult2 AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') as RowNum, * FROM TempResult
)
SELECT top (@LastRec-1) *
FROM TempResult2
WHERE RowNum > @FirstRec 
AND RowNum < @LastRec
'
print @sql
EXEC sp_executesql @sql
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
END
GO